<?php

namespace app\admin\controller\benefit;

use app\admin\mode\TestDBA01 as ModeTestDBA01;
use think\App;
use app\Request;
use think\Validate;
use think\facade\Log;
use think\response\Json;
use think\facade\Cache;
use think\exception\ValidateException;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Exception;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

///--------生成测试数据--------
use app\admin\model\BenefitZYF;
use app\admin\model\BenefitZYZC;
use app\admin\model\EpbpCyl;
use DateInterval;
use DateTime;
//------------------------------

use app\common\controller\AdminController;
use app\admin\service\annotation\ControllerAnnotation;
use app\admin\service\annotation\NodeAnnotation;
use app\admin\service\UploadService;
use app\admin\model\BenefitWellNumber;
use app\admin\model\SystemUploadfile;

#[ControllerAnnotation(title: '效益计算')]
class Calculate extends AdminController
{
    private array $notes;

    private array $rule = [
        'start'  => 'require',
        'end'   => 'require'
    ];
    private array $message = [
        'start.require'   => '请选择开始日期',
        'end.require'   => '请选择结束日期'
    ];

    #[NodeAnnotation(ignore: ['modify', 'recycle'])]
    protected array $ignoreNode;

    public function __construct(App $app)
    {
        parent::__construct($app);
        self::$model = new \app\admin\model\BenefitCalculate();
    }

    #[NodeAnnotation(title: '列表', auth: true)]
    public function index(Request $request): Json|string
    {
        if ($request->isAjax()) {
            if (input('selectFields')) {
                return $this->selectList();
            }
            list($page, $limit, $where) = $this->buildTableParams();
            $count = self::$model::where($where)->count();
            $list  = self::$model::withoutField('password')
                ->where($where)
                ->page($page, $limit)
                ->order($this->sort)
                ->select()->toArray();
            $data  = [
                'code'  => 0,
                'msg'   => '',
                'count' => $count,
                'data'  => $list,
            ];
            return json($data);
        }

        return $this->fetch();
    }

    #[NodeAnnotation(title: '计算', auth: true)]
    public function calculate(Request $request)
    {
        Cache::set('upload_file', '1759105933qUB1', 60);
        $file = Cache::get('upload_file');
        if (empty($file)) {
            $this->error('请先上传文件后再计算');
        }
        $well_number_array = BenefitWellNumber::where(array('file' => $file))->select();
        $file = SystemUploadfile::where(array('new_name' => $file))->find();
        if (empty($file)) {
            $this->error('Excel文件信息异常，请重新上传');
        }
        $info = array(
            'well_number_count' => count($well_number_array),
            'calculate_date' => $file->calculate_date,
            'date_range' => $file->date_range,
        );
        $this->assign('info', $info);
        return $this->fetch();
    }

    #[NodeAnnotation(title: '计算井号数量', auth: true)]
    public function calculateWellNumberCount(Request $request)
    {
        $post  = $request->post();
        $this->validate($post, $this->rule, $this->message);
    }

    #[NodeAnnotation(title: '上传', auth: true)]
    public function upload(Request $request)
    {
        if ($request->isPost()) {
            $files = request()->file();
            if (empty($files)) {
                $this->error("Excel文件不能为空");
                return;
            }
            $post = request()->post();
            $this->validate($post, $this->rule, $this->message);

            $uploadConfig = sysConfig('upload');
            $rule = [
                'file|文件' => "require|file|fileExt:{$uploadConfig['upload_allow_ext']}|fileSize:{$uploadConfig['upload_allow_size']}",
            ];
            $message = [
                'file.fileExt' => '请上传正确的Excel文件'
            ];

            $this->validate($files, $rule, $message); //校验文件类型
            $range = $post['start'] . '/' . $post['end'];
            $upload_type = $uploadConfig['upload_type'];
            $uploadConfig['date_range'] = $range;
            $upload = array();
            $code = 0;
            try {
                $msg = '上传失败';
                $upload = UploadService::instance()->setConfig($uploadConfig)->$upload_type($files['file'], 'upload'); //上传
                $code = $upload['code'] ?? 0;
                if ($code == 0) {
                    $msg = "上传失败，请稍后再试";
                } else {
                    $msg = "上传成功";
                }
                $upload['msg'] = $msg;
            } catch (\Exception $e) {
                $msg = $e->getMessage();
            }
            if ($code == 1) {
                //文件上传成功，下面开始提取数据
                $upload_data = $upload['data'];
                $publicPath = app()->getRootPath() . 'public' . DIRECTORY_SEPARATOR;
                // 拼接具体文件路径（例如读取public下的test.txt）
                $file_path = $publicPath . $upload_data['url'];
                // // 检查文件是否存在
                if (!file_exists($file_path)) {
                    $upload['code'] = 0;
                    $upload['msg'] = "Excel不存在，请重新上传";
                    return json($upload);
                }
                $new_name = $upload['data']['name'];
                $data = $this->readExcelData($file_path, $new_name, $range);
                if (count($data) > 0) {
                    $this->save($data);
                    $upload['code'] = 1;
                    $upload['msg'] = "文件上传成功";
                    $time = 1800;
                    Cache::set('upload_file', $new_name, $time);
                    Cache::tag('Wellnumber')->set($new_name, $data, $time);
                    return json($upload);
                } else {
                    $upload['code'] = 1;
                    $upload['msg'] = "记录为空，无需计算";
                    $this->deletefile($new_name);
                    return json($upload);
                }
            } else {
                $upload['code'] = 0;
                $upload['msg'] = $msg;
                return json($upload);
            }
        }
        $calculate = array(
            'type' => benefitConfig('calculate_month_type'),
            'start' => benefitConfig('calculate_start_day'),
            'interval' => benefitConfig('calculate_day_Interval')
        );
        $this->assign('calculate', $calculate);
        return $this->fetch('upload');
    }

    /**
     * 读取Excel文件数据
     * @param string $filePath Excel文件路径
     * @return array|false 成功返回数据数组，失败返回false
     */
    function readExcelData($filePath, $fileName, $range)
    {
        try {
            // 根据文件后缀自动选择读取器
            $reader = IOFactory::createReaderForFile($filePath);

            // 设置只读模式（提高性能）
            $reader->setReadDataOnly(true);

            // 加载Excel文件
            $spreadsheet = $reader->load($filePath);

            // 获取活动工作表（默认第一个工作表）
            $worksheet = $spreadsheet->getActiveSheet();

            // 获取最大行数和列数
            $highestRow = $worksheet->getHighestRow();
            //$highestRow = 10;
            $highestColumn = $worksheet->getHighestColumn();
            //$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
            $saveColumnArray = config('upload.save_column');
            $filterArray = config('upload.filter');
            $filterkeys = array_keys($filterArray);
            $excelMap = config('upload.excel_map');
            $startRow = config('upload.start_row') ?? 1;
            $dateColumn = config('upload.date_column');
            $data = [];
            // 循环读取数据（从第1行开始）
            for ($row = $startRow; $row <= $highestRow; $row++) {
                $rowData = [];
                $fileter_res = [];
                for ($i = 0; $i < count($filterkeys); $i++) {
                    $filter_key = $filterkeys[$i];
                    $filter_content = $filterArray[$filter_key];
                    $cell = $worksheet->getCell($filter_key . strval($row));
                    $value = $cell->getFormattedValue();
                    if (in_array($value, $filter_content)) {
                        array_push($fileter_res, 'true');
                    } else {
                        array_push($fileter_res, 'false');
                    }
                }
                if (in_array('false', $fileter_res)) {
                    continue;
                }
                $rowData = [];
                $res = empty($range);
                $filter = !empty($range);
                foreach ($saveColumnArray as $col) {
                    $cell = $worksheet->getCell($col . strval($row));
                    if (in_array($col, $dateColumn)) {
                        $cell->getStyle()->getNumberFormat()->setFormatCode(
                            NumberFormat::FORMAT_DATE_YYYYMMDD
                        );
                    }
                    $isDate = Date::isDateTime($cell);
                    $value = null;
                    if ($isDate) {
                        $dateTime = Date::excelToDateTimeObject($cell->getValue());
                        $value = $dateTime->format('Y-m-d');
                        if ($filter) {
                            $rangeArray = explode('/', $range);
                            $startDate = $rangeArray[0];
                            $endDate = $rangeArray[1];
                            if ($col == 'K') {
                                // 目标日期
                                $targetDate = $value;
                                // 日期范围
                                $targetTimestamp = strtotime($targetDate);
                                $startTimestamp = strtotime($startDate);
                                $endTimestamp = strtotime($endDate);
                                if ($targetTimestamp >= $startTimestamp && $targetTimestamp <= $endTimestamp) {
                                    $res = true;
                                }
                            }
                        }
                    } else {
                        $value = $cell->getFormattedValue();
                    }

                    if (isset($excelMap[$col])) {
                        $rowData[$excelMap[$col]] = $this->formatNumeric($value);
                    }
                    $rowData['file'] = $fileName;
                }
                if ($res) {
                    $data[] = $rowData;
                }
            }

            return $data;
        } catch (Exception $e) {

            return false;
        }
    }

    ///格式化数字，读取的日期列获取到的是数字，还原成日期
    private function formatNumeric($cellValue)
    {
        if (is_numeric($cellValue)) {
            // 检查是否可能是日期（Excel日期范围大致在25569到44927之间）
            if ($cellValue > 25569 && $cellValue < 44927) {
                // 转换为DateTime对象
                $dateTime = Date::excelToDateTimeObject($cellValue);
                $formattedDate = $dateTime->format('Y-m-d'); // 自定义格式
            } else {
                // 不是日期，直接使用原值
                $formattedDate = $cellValue;
            }
            return $formattedDate;
        } else {
            return $cellValue;
        }
    }

    // 提取的井号入库
    private function save($data)
    {
        $model = new BenefitWellNumber();
        $batchSize = 500; // 每批插入数量
        $total = count($data);
        for ($i = 0; $i < $total; $i += $batchSize) {
            // 截取批次数据
            $batchData = array_slice($data, $i, $batchSize);
            $model->insertAll($batchData);
        }
    }

    // 删除文件
    private function deletefile($name)
    {
        $model = SystemUploadfile::where('new_name', $name)->find();
        if (!empty($model)) {
            $path = $model->url;
            try {
                $filePath = app()->getRootPath() . 'public/' . $path;
                // 假设文件存储在public磁盘（配置在config/filesystem.php中）
                $fileRelativePath = str_replace("/storage/", "", $path);; // 相对于磁盘根目录的路径
                // // 检查文件是否存在
                if (file_exists($filePath)) {
                    Log::info("文件存在");
                    unlink($filePath);
                } else {
                    Log::info("文件不存在");
                }
            } catch (ValidateException $e) {
                Log::error($e->getMessage());
            }
            $model->delete();
        }
    }


    ///生成测试数据

    #[NodeAnnotation(title: '作业费', auth: true)]
    public function createZYFData()
    {
        $file = "1759105933qUB1";
        $well_number_array = BenefitWellNumber::where(array('file' => $file))->select();
        $data = [];
        for ($j = 0; $j < count($well_number_array); $j++) {
            $model = $well_number_array[$j];
            $start_date = new DateTime($model->start_date);
            $end_date = new DateTime($model->end_date);
            $sum_je = BenefitZYZC::where('F_JH', $model->well_number)->sum('F_JE');
            $item['F_GCBH'] = "C20241018" . $model->well_number;
            $item['F_KGRQ'] = $start_date->format("Ymd");
            $item['F_WGRQ'] = $end_date->format("Ymd");
            $item['F_GCMC'] = $model->well_number;
            $item['F_DMBTR'] = $sum_je;
            $data[] = $item;
        }
        $this->saveZYFData($data);
        return json($data);
    }

    private function saveZYFData($data)
    {
        $model = new BenefitZYF();
        $batchSize = 500; // 每批插入数量
        $total = count($data);
        for ($i = 0; $i < $total; $i += $batchSize) {
            // 截取批次数据
            $batchData = array_slice($data, $i, $batchSize);
            $model->insertAll($batchData);
        }
    }

    #[NodeAnnotation(title: '材料费数据', auth: true)]
    public function createZYZCData()
    {
        $start_time = time();
        $file = '1759105933qUB1';
        $well_number_array = BenefitWellNumber::where(array('file' => $file))->select();
        // echo '生成材料费测试数据。' . '共' . count($well_number_array) . '台井';
        for ($j = 0; $j < count($well_number_array); $j++) {
            $model = $well_number_array[$j];
            $model->zyf =  $model->zyf()->where(array('F_KGRQ' => str_replace('-', '', $model->start_date), 'F_WGRQ' => str_replace('-', '', $model->end_date)))->find();
            $start = $this->formatString($model->start_date);
            $end = $this->formatString($model->end_date);
            $array = [$start];
            if ($start != $end) {
                $array = $this->addMonthInterval($model->start_date, $model->end_date);
            }
            $model->clf = $model->clf()->where(function ($query) use ($array) {
                // 在闭包内统一管理OR条件
                foreach ($array as $key => $keyword) {
                    // 第一个条件用where，后续用whereOr
                    if ($key == 0) {
                        $query->where('F_FLSJ', 'like', "{$keyword}");
                    } else {
                        $query->whereOr('F_FLSJ', 'like', "{$keyword}");
                    }
                }
            })->select();
        }
        $time_interval = time() - $start_time;
        Log::info($time_interval);
        return json($well_number_array);
        for ($j = 0; $j < count($well_number_array); $j++) {
            $model = $well_number_array[$j];
            $days = $this->date_diff($model->start_date, $model->end_date);
            $start_date = new DateTime($model->start_date);
            $data = [];
            $data2 = [];
            for ($i = 0; $i <= $days; $i++) {
                $interval = new DateInterval("P{$i}D");
                $date = new DateTime($model->start_date);
                $date->add($interval);
                $date_str = $date->format("Y-m-d");
                //var_dump("增加{$i}天后: " . $date->format("Y-m-d") . "\n");
                $secureRandomInt = 1; //1-小数  2-整数
                $feiyong = 997.2;
                if (function_exists('random_int')) {
                    $secureRandomInt = random_int(1, 2);
                }
                $min = 440.7;
                $max = 15876.93;
                if ($secureRandomInt == 2) {
                    $feiyong = mt_rand(intval($min), intval($max));
                } else {
                    $rangeRandomFloat = $min + ($max - $min) * rand() / getrandmax();
                    // 保留4位小数
                    $feiyong = number_format($rangeRandomFloat, 4, '.', '');
                }
                $well_number = $model->well_number;
                $item['F_FLSJ'] = $date_str;
                $item['F_JH'] = $well_number;
                $item['F_JE'] = $feiyong;
                $data[] = $item;

                $min = 15.0;
                $max = 20.0;
                $rangeRandomFloat = $min + ($max - $min) * rand() / getrandmax();
                $rcyl = number_format($rangeRandomFloat, 1, '.', '');

                $min = 10.0;
                $max = 30.0;
                $rangeRandomFloat = $min + ($max - $min) * rand() / getrandmax();
                $hs = number_format($rangeRandomFloat, 1, '.', '');
                $item2['JH'] = $well_number;
                $item2['RCYL1'] = $rcyl;
                $item2['HS'] = $hs;
                $item2['RQ'] = $date_str;
                $data2[] = $item2;
            }
            $this->saveZYZCData($data);
            $this->saveCLData($data2);
        }
        return json(array());
    }

    private function formatString($date)
    {
        $array = explode('-', $date);
        return '%' . $array[0] . '-' . $array[1] . '%';
    }

    ///加一个月
    private function addMonthInterval($start,$end)
    {
        // 解析起始日期为当月第一天
        $startDate = DateTime::createFromFormat('Y-m-d', $start);
        $startDate->modify('first day of this month');

        // 解析结束日期并取其月份的第一天
        $endDate = new DateTime($end);
        //$endDate->modify('first day of this month');

        // 存储结果的数组
        $firstDays = [];

        // 循环获取每个月的第一天
        $currentDate = clone $startDate;
        
        while ($currentDate <= $endDate) {
            $content = '%'.$currentDate->format('Y-m').'%';
            $firstDays[] = $content;
            // 移至下个月的第一天
            $currentDate->modify('first day of next month');
        }
        return $firstDays;
    }

    /**
     * 计算两个日期之间相差的月份数
     * @param string $date1 起始日期（格式：Y-m-d）
     * @param string $date2 结束日期（格式：Y-m-d）
     * @param bool $strict 是否严格按照完整月份计算（默认true）
     * @return int|false 相差的月份数，失败返回false
     */
    function calculateMonthDifference($date1, $date2, $strict = true)
    {
        // 解析日期
        $d1 = DateTime::createFromFormat('Y-m-d', $date1);
        $d2 = DateTime::createFromFormat('Y-m-d', $date2);

        // 验证日期有效性
        if (!$d1 || !$d2) {
            return false;
        }

        // 确保d1是较早的日期
        if ($d1 > $d2) {
            list($d1, $d2) = array($d2, $d1);
        }

        $yearDiff = (int)$d2->format('Y') - (int)$d1->format('Y');
        $monthDiff = (int)$d2->format('m') - (int)$d1->format('m');
        $totalMonths = $yearDiff * 12 + $monthDiff;

        // 严格模式：如果日期不足一个完整月，减1
        if ($strict) {
            $day1 = (int)$d1->format('d');
            $day2 = (int)$d2->format('d');

            // 处理月末特殊情况（如1月31日到2月28日视为一个月）
            if ($day2 < $day1) {
                // 检查是否是月末最后一天
                $lastDayOfMonth = (int)$d2->format('t');
                if ($day2 != $lastDayOfMonth) {
                    $totalMonths--;
                }
            }
        }

        return $totalMonths;
    }

    //日期差值
    private function date_diff($start, $end)
    {
        try {
            // 将字符串转换为DateTime对象
            $start_date = new DateTime($start);
            $end_date = new DateTime($end);

            // 计算日期差值
            $interval = $end_date->diff($start_date);

            // 也可以只获取总天数差异
            $totalDays = $interval->days;
            return $totalDays;
        } catch (Exception $e) {
            return 0;
        }
    }

    private function saveZYZCData($data)
    {
        $model = new BenefitZYZC();
        $batchSize = 500; // 每批插入数量
        $total = count($data);
        for ($i = 0; $i < $total; $i += $batchSize) {
            // 截取批次数据
            $batchData = array_slice($data, $i, $batchSize);
            $model->insertAll($batchData);
        }
    }

    private function saveCLData($data)
    {
        $model = new EpbpCyl();
        $batchSize = 500; // 每批插入数量
        $total = count($data);
        for ($i = 0; $i < $total; $i += $batchSize) {
            // 截取批次数据
            $batchData = array_slice($data, $i, $batchSize);
            $model->insertAll($batchData);
        }
    }
}
class FileValidate extends Validate
{
    protected $rule =   [
        'file'  => 'fileExt:xlsx,xls'
    ];

    protected $message  =   [
        'file.fileExt' => '请上传正确的Excel文件',
    ];
}
